﻿------------------------------------------------
-- CSDL cho Project Tham Dinh Gia
-- Ngay khoi tao: 20-4-2011 
-- Update 1: 27-4-2011 : sua lai theo yeu cau moi, cap nhat bang tham so, them thong tin GPS
------------------------------------------------
--DROP ALL TABLES IF THEY ARE EXISTS
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CO_SO_HA_TANG') AND type = (N'U'))
DROP TABLE CO_SO_HA_TANG
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CONG_TRINH_XAY_DUNG') AND type = (N'U'))
DROP TABLE CONG_TRINH_XAY_DUNG
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'GIA_THAM_DINH') AND type = (N'U'))
DROP TABLE GIA_THAM_DINH
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'THONG_TIN_VI_TRI') AND type = (N'U'))
DROP TABLE THONG_TIN_VI_TRI
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'THUA_DAT') AND type = (N'U'))
DROP TABLE THUA_DAT
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'VAT_LIEU_XAY_DUNG') AND type = (N'U'))
DROP TABLE VAT_LIEU_XAY_DUNG
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'HO_SO_THAM_DINH') AND type = (N'U'))
DROP TABLE HO_SO_THAM_DINH
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'DUONG_KHAO_SAT') AND type = (N'U'))
DROP TABLE DUONG_KHAO_SAT
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'THAM_SO') AND type = (N'U'))
DROP TABLE THAM_SO
GO
--------------------------------------------------------------------
-- Tao bang THAM_SO
CREATE TABLE THAM_SO (
	MA_THAM_SO BIGINT IDENTITY(1, 1) PRIMARY KEY,
	PARENT_ID BIGINT,
	LOAI_THAM_SO NVARCHAR(50),
	NOI_DUNG NVARCHAR(255),
	SEQUENCE INT,
	GHI_CHU NVARCHAR(50),
)
--------------------------------------------------------------------

-- Tao cac bang cho viec nhap danh sach quan-phuong-duong-doan duong
-- Tao bang DUONG KHAO SAT
-- Nen tao mot form rieng de xay dung bang DUONG KHAO SAT
-- Co cac loai tham so dia chi la : LOAI_QUAN va LOAI_PHUONG

INSERT INTO THAM_SO VALUES (0,'LOAI_QUAN','Binh Thanh',1,NULL); --ID: 1
INSERT INTO THAM_SO VALUES (0,'LOAI_PHUONG','Phuong 1',1,NULL); --ID: 2

-- Tao bang DUONG_KHAO_SAT
CREATE TABLE DUONG_KHAO_SAT (
	MA_DKS BIGINT IDENTITY(1, 1) PRIMARY KEY,
	QUAN BIGINT,	 --  lay tu bang THAM SO
	PHUONG BIGINT,	 --  lay tu bang THAM SO
	DUONG NVARCHAR(50),
	DOAN_DUONG NVARCHAR(50),
	NGAY_KHOI_TAO DATETIME,
	ACTIVE_FLAG CHAR(1) DEFAULT 'Y'	
	FOREIGN KEY (QUAN) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (PHUONG) REFERENCES THAM_SO(MA_THAM_SO)
)
INSERT INTO DUONG_KHAO_SAT VALUES (1, 2, 'Dinh Tien Hoang', 'Dau Duong', CURRENT_TIMESTAMP, 'Y')




--------------------------------------------------------------------
-- Tao cac bang phuc vu cho viec luu tru ho so
-- Tao bang HO SO THAM DINH
INSERT INTO THAM_SO VALUES (0,'LOAI_TINH_TRANG_PHAP_LY','Giấy hường',1,NULL);	--ID: 3
INSERT INTO THAM_SO VALUES (0,'LOAI_TINH_TRANG_PHAP_LY','Giấy đỏ',2,NULL);	--ID: 4
INSERT INTO THAM_SO VALUES (0,'LOAI_TINH_TRANG_PHAP_LY','Giấy hồng',3,NULL);	--ID: 5
INSERT INTO THAM_SO VALUES (0,'LOAI_TINH_TRANG_PHAP_LY','Khác',4,NULL);		--ID: 6

CREATE TABLE HO_SO_THAM_DINH (
	MA_HO_SO BIGINT IDENTITY(1, 1) PRIMARY KEY ,
	MA_DKS BIGINT ,
	SO_PHIEU NVARCHAR(255),
	TEN_CHU_SU_DUNG NVARCHAR(255),
	DIA_CHI NVARCHAR(255),
	GPS_LATITUDE float,
	GPS_LONGTITUDE float,
	GPS_CHI_CHU NVARCHAR(255),
	TINH_TRANG_PHAP_LY BIGINT, --  lay tu bang THAM SO
	NOI_DUNG_PHAP_LY NVARCHAR(255),	
	LOAI_KHU_VUC_KINH_DOANH INT,
	Y_KIEN_THAM_DINH NVARCHAR(1023),
	GIA_DAT_1 float,
	GIA_DAT_2 float,
	GIA_DAT_BINH_QUAN_1 float,
	GIA_DAT_BINH_QUAN_2 float,
	NGUOI_KHOI_TAO uniqueidentifier ,
	NGAY_KHOI_TAO DATETIME,
	NGAY_CAP_NHAT DATETIME,
	ACTIVE_FLAG CHAR(1) DEFAULT 'Y',
	FOREIGN KEY (MA_DKS) REFERENCES DUONG_KHAO_SAT(MA_DKS),
	FOREIGN KEY (NGUOI_KHOI_TAO) REFERENCES aspnet_Users(UserId),
	FOREIGN KEY (TINH_TRANG_PHAP_LY) REFERENCES THAM_SO(MA_THAM_SO)
)
INSERT INTO HO_SO_THAM_DINH VALUES (1,'3','Le Sum 3','Chi Hoa',51.5,-0.1167,'k co ghi chu',3,NULL,1,'Nha xap xe',0,0,0,0,'55BDE91C-7B70-4CFE-A5D6-90BF88DB7AEF',CURRENT_TIMESTAMP,NULL,'Y')
------------------------------------------------
-- Tao bang GIA_THAM_DINH
INSERT INTO THAM_SO VALUES (0,'LOAI_THANH_TOAN','Trả ngay',1,NULL);	--ID: 7
INSERT INTO THAM_SO VALUES (0,'LOAI_THANH_TOAN','Trả chậm',2,NULL);	--ID: 8
INSERT INTO THAM_SO VALUES (0,'LOAI_THANH_TOAN','Khác',3,NULL);		--ID: 9

CREATE TABLE GIA_THAM_DINH (
	MA_HO_SO BIGINT PRIMARY KEY,
	GIA_GIAO_DICH_TRIEU_DONG FLOAT,
	GIA_GIAO_DICH_CAY_VANG FLOAT,
	THOI_DIEM_GIAO_DICH DATETIME,
	LOAI_THANH_TOAN BIGINT, --  lay tu bang THAM SO
	NOI_DUNG_THANH_TOAN NVARCHAR(50),
	FOREIGN KEY (MA_HO_SO) REFERENCES HO_SO_THAM_DINH(MA_HO_SO),
	FOREIGN KEY (LOAI_THANH_TOAN) REFERENCES THAM_SO(MA_THAM_SO)
)
INSERT INTO GIA_THAM_DINH VALUES (1,10000,1,CURRENT_TIMESTAMP,7,NULL)

------------------------------------------------
-- Tao bang THONG TIN VI TRI
CREATE TABLE THONG_TIN_VI_TRI (
	MA_HO_SO BIGINT PRIMARY KEY,
	CACH_TRUNG_TAM FLOAT,
	CACH_DUONG_CHINH FLOAT,
	FOREIGN KEY (MA_HO_SO) REFERENCES HO_SO_THAM_DINH(MA_HO_SO)
)
INSERT INTO THONG_TIN_VI_TRI VALUES (1,10000,10000)

------------------------------------------------
-- Tao bang THUA_DAT

INSERT INTO THAM_SO VALUES (0,'LOAI_HINH_DANG_THUA_DAT','Vuông',1,NULL);		--ID: 10
INSERT INTO THAM_SO VALUES (0,'LOAI_HINH_DANG_THUA_DAT','Tóp',2,NULL);		--ID: 11
INSERT INTO THAM_SO VALUES (0,'LOAI_HINH_DANG_THUA_DAT','Nở hậu',3,NULL);		--ID: 12
INSERT INTO THAM_SO VALUES (0,'LOAI_HINH_DANG_THUA_DAT','Khác',4,NULL);		--ID: 13

INSERT INTO THAM_SO VALUES (0,'LOAI_DUONG_PHO','Mặt tiền',1,NULL);		--ID: 14
INSERT INTO THAM_SO VALUES (0,'LOAI_DUONG_PHO','Hẻm',2,NULL);				--ID: 15

INSERT INTO THAM_SO VALUES (0,'LOAI_LAN_DUONG','Một chiều',1,NULL);				--ID: 16
INSERT INTO THAM_SO VALUES (0,'LOAI_LAN_DUONG','Hai chiều',2,NULL);				--ID: 17
INSERT INTO THAM_SO VALUES (0,'LOAI_LAN_DUONG','Có dãy phân cách',3,NULL);		--ID: 18

INSERT INTO THAM_SO VALUES (0,'LOAI_HEM','Cấp 1',1,NULL);		--ID: 19
INSERT INTO THAM_SO VALUES (0,'LOAI_HEM','Cấp 2',2,NULL);		--ID: 20
INSERT INTO THAM_SO VALUES (0,'LOAI_HEM','Cấp 3',3,NULL);		--ID: 21
INSERT INTO THAM_SO VALUES (0,'LOAI_HEM','Cấp 4',4,NULL);		--ID: 22

CREATE TABLE THUA_DAT (
	MA_HO_SO BIGINT PRIMARY KEY,
	SO_THUA_DAT NVARCHAR(50),
	TO_BAN_DO NVARCHAR(50),
	CHIEU_DAI FLOAT,
	CHIEU_RONG FLOAT,
	TONG_DIEN_TICH FLOAT,
	DIEN_TICH_CONG_NHAN FLOAT,
	DIEN_TICH_KHONG_CONG_NHAN FLOAT,
	DIEN_TICH_SU_DUNG_CHUNG FLOAT,
	LOAI_HINH_DANG_THUA_DAT BIGINT,		--  lay tu bang THAM SO
	HINH_DANG_THUA_DAT NVARCHAR(50),	
	LOAI_DUONG_PHO BIGINT,				--  lay tu bang THAM SO
	LO_GIOI FLOAT,	
	SO_MAT_TIEN NVARCHAR(50),
	LOAI_LAN_DUONG BIGINT,				--  lay tu bang THAM SO
	NOI_DUNG_LAN_DUONG NVARCHAR(50),
	LOAI_HEM BIGINT,					--  lay tu bang THAM SO
	FOREIGN KEY (MA_HO_SO) REFERENCES HO_SO_THAM_DINH(MA_HO_SO),
	FOREIGN KEY (LOAI_HINH_DANG_THUA_DAT) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_DUONG_PHO) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_LAN_DUONG) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_HEM) REFERENCES THAM_SO(MA_THAM_SO)
	
)
INSERT INTO THUA_DAT VALUES (1,'123456','123456',40,40,1600,1600,0,1600,10,NULL,14,1,NULL,16,NULL,19)

------------------------------------------------
-- Tao bang CO SO HA TANG
CREATE TABLE CO_SO_HA_TANG (
	MA_HO_SO BIGINT PRIMARY KEY,
	DONG_HO_DIEN INT,
	CAP_TREO INT,
	CAP_NGAM INT,
	DONG_HO_NUOC INT,
	AP_LUC_NUOC INT,
	CHAT_LUONG_NUOC INT,
	TIENG_ON INT,
	KHOI_BUI INT,
	TINH_TRANG_NGAP INT,
	HE_THONG_CHIEU_SANG INT,
	CHOT_BAO_VE INT,
	AN_NINH INT,
	GAN_CO_SO_GIAO_DUC INT,
	GAN_CO_SO_Y_TE INT,
	GAN_CHO_TRUNG_TAM INT,
	GAN_CO_SO_GIAI_TRI INT,
	GAN_DICH_VU_CONG_CONG INT,
	GAN_THONG_TIN_LIEN_LAC INT,
	FOREIGN KEY (MA_HO_SO) REFERENCES HO_SO_THAM_DINH(MA_HO_SO)
)
INSERT INTO CO_SO_HA_TANG VALUES (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)

------------------------------------------------
-- Tao bang CONG TRINH XAY DUNG
INSERT INTO THAM_SO VALUES (0,'LOAI_NHA','Biệt thự',1,NULL);		--ID: 23
INSERT INTO THAM_SO VALUES (0,'LOAI_NHA','Phố',2,NULL);				--ID: 24
INSERT INTO THAM_SO VALUES (0,'LOAI_NHA','Liền kề',3,NULL);			--ID: 25
INSERT INTO THAM_SO VALUES (0,'LOAI_NHA','Chung cư',4,NULL);		--ID: 26
INSERT INTO THAM_SO VALUES (0,'LOAI_NHA','Khác',5,NULL);			--ID: 27

INSERT INTO THAM_SO VALUES (0,'LOAI_CAP_NHA','Cấp 1',1,NULL);			--ID: 28
INSERT INTO THAM_SO VALUES (0,'LOAI_CAP_NHA','Cấp 2',2,NULL);			--ID: 29
INSERT INTO THAM_SO VALUES (0,'LOAI_CAP_NHA','Cấp 3',3,NULL);			--ID: 30
INSERT INTO THAM_SO VALUES (0,'LOAI_CAP_NHA','Cấp 4',4,NULL);			--ID: 31
INSERT INTO THAM_SO VALUES (0,'LOAI_CAP_NHA','Tạm',5,NULL);				--ID: 32

INSERT INTO THAM_SO VALUES (0,'HUONG_NHA','Đ',1,NULL);			--ID: 33
INSERT INTO THAM_SO VALUES (0,'HUONG_NHA','ĐB',2,NULL);			--ID: 34
INSERT INTO THAM_SO VALUES (0,'HUONG_NHA','T',3,NULL);			--ID: 35
INSERT INTO THAM_SO VALUES (0,'HUONG_NHA','TB',4,NULL);			--ID: 36
INSERT INTO THAM_SO VALUES (0,'HUONG_NHA','N',5,NULL);			--ID: 37
INSERT INTO THAM_SO VALUES (0,'HUONG_NHA','ĐN',6,NULL);			--ID: 38
INSERT INTO THAM_SO VALUES (0,'HUONG_NHA','B',7,NULL);			--ID: 39
INSERT INTO THAM_SO VALUES (0,'HUONG_NHA','TN',8,NULL);			--ID: 40

CREATE TABLE CONG_TRINH_XAY_DUNG (
	MA_HO_SO BIGINT PRIMARY KEY,
	LOAI_NHA BIGINT,				--  lay tu bang THAM SO
	NOI_DUNG_LOAI_NHA NVARCHAR(50),
	LOAI_CAP_NHA BIGINT,			--  lay tu bang THAM SO
	HUONG_NHA BIGINT,				--  lay tu bang THAM SO
	GIAY_PHEP_XAY_DUNG char(1),		--  gia tri Y hoac N
	DIEN_TICH_XD_CONG_NHAN FLOAT,
	DIEN_TICH_XD_KHONG_CONG_NHAN FLOAT,
	NAM_XAY_DUNG DATETIME,
	SO_NAM_SU_DUNG FLOAT,
	DON_GIA_XAY_DUNG FLOAT,
	TUOI_DOI_KINH_TE FLOAT,
	GIA_TRI_CONG_TRINH FLOAT,
	SO_LAN_SUA_CHUA INT,
	FOREIGN KEY (MA_HO_SO) REFERENCES HO_SO_THAM_DINH(MA_HO_SO),
	FOREIGN KEY (LOAI_NHA) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_CAP_NHA) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (HUONG_NHA) REFERENCES THAM_SO(MA_THAM_SO)
)
INSERT INTO CONG_TRINH_XAY_DUNG VALUES (1,23,NULL,28,33,1,10,10,CURRENT_TIMESTAMP,1,1,1,1,1)


------------------------------------------------
-- Tao bang VAT_LIEU_XAY_DUNG
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_KHUNG','BTCT',1,NULL);		--ID: 41
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_KHUNG','Gạch',2,NULL);		--ID: 42
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_KHUNG','Gõ',3,NULL);			--ID: 43
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_KHUNG','Khác',4,NULL);		--ID: 44

INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_MONG','Cọc',1,NULL);		--ID: 45
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_MONG','Bằng',2,NULL);		--ID: 46
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_MONG','Bè',3,NULL);		--ID: 47
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_MONG','Khác',4,NULL);		--ID: 48

INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_TUONG_NHA','Gạch',1,NULL);	--ID: 49
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_TUONG_NHA','Gỗ',2,NULL);		--ID: 50
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_TUONG_NHA','Tole',3,NULL);	--ID: 51
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_TUONG_NHA','Khác',4,NULL);	--ID: 52

INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_NEN','Gạch',1,NULL);		--ID: 53
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_NEN','Gỗ',2,NULL);		--ID: 54
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_NEN','Xi măng',3,NULL);	--ID: 55
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_NEN','Khác',4,NULL);		--ID: 56

INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_MAI','Bằng',1,NULL);		--ID: 57
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_MAI','Ngói',2,NULL);		--ID: 58
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_MAI','Tole',3,NULL);		--ID: 59
INSERT INTO THAM_SO VALUES (0,'LOAI_KET_CAU_MAI','Khác',4,NULL);		--ID: 60

INSERT INTO THAM_SO VALUES (0,'LOAI_TRANG_TRI_NOI_THAT','Cao cấp',1,NULL);	--ID: 61
INSERT INTO THAM_SO VALUES (0,'LOAI_TRANG_TRI_NOI_THAT','Khá',2,NULL);		--ID: 62
INSERT INTO THAM_SO VALUES (0,'LOAI_TRANG_TRI_NOI_THAT','Trung bình',3,NULL);	--ID: 63
INSERT INTO THAM_SO VALUES (0,'LOAI_TRANG_TRI_NOI_THAT','Tạm',4,NULL);		--ID: 64

INSERT INTO THAM_SO VALUES (0,'LOAI_CONG_TRINH_XAY_DUNG','Sân thượng',1,NULL);--ID: 65
INSERT INTO THAM_SO VALUES (0,'LOAI_CONG_TRINH_XAY_DUNG','Hồ bơi',2,NULL);	--ID: 66
INSERT INTO THAM_SO VALUES (0,'LOAI_CONG_TRINH_XAY_DUNG','Sân',3,NULL);		--ID: 67
INSERT INTO THAM_SO VALUES (0,'LOAI_CONG_TRINH_XAY_DUNG','Khác',4,NULL);		--ID: 68


CREATE TABLE VAT_LIEU_XAY_DUNG (
	MA_HO_SO BIGINT PRIMARY KEY,
	LOAI_KET_CAU_KHUNG BIGINT,					--  lay tu bang THAM SO
	NOI_DUNG_KET_CAU_KHUNG NVARCHAR(50),
	LOAI_KET_CAU_MONG BIGINT,					--  lay tu bang THAM SO
	NOI_DUNG_KET_CAU_MONG NVARCHAR(50),
	LOAI_KET_CAU_TUONG_NHA BIGINT,				--  lay tu bang THAM SO
	NOI_DUNG_KET_CAU_TUONG_NHA NVARCHAR(50),
	LOAI_KET_CAU_NEN BIGINT,					--  lay tu bang THAM SO
	NOI_DUNG_KET_CAU_NEN NVARCHAR(50),
	LOAI_KET_CAU_MAI BIGINT,					--  lay tu bang THAM SO
	NOI_DUNG_KET_CAU_MAI NVARCHAR(50),
	CHAT_LUONG_MONG FLOAT, 
	CHAT_LUONG_SAN FLOAT,
	CHAT_LUONG_TUONG FLOAT,
	CHAT_LUONG_TRAN FLOAT,
	CHAT_LUONG_MAI FLOAT,	
	TI_LE_KET_CAU_MONG FLOAT, 
	TI_LE_KET_CAU_SAN FLOAT,
	TI_LE_KET_CAU_TUONG FLOAT,
	TI_LE_KET_CAU_TRAN FLOAT,
	TI_LE_KET_CAU_MAI FLOAT,
	SO_PHONG_NGU INT,
	SO_NHA_VE_SINH INT,
	LOAI_TRANG_TRI_NOI_THAT BIGINT,				--  lay tu bang THAM SO
	NOI_DUNG_TRANG_TRI_NOI_THAT NVARCHAR(50),
	LOAI_CONG_TRINH_XAY_DUNG BIGINT,			--  lay tu bang THAM SO
	NOI_DUNG_CONG_TRINH_XAY_DUNG NVARCHAR(50),
	FOREIGN KEY (MA_HO_SO) REFERENCES HO_SO_THAM_DINH(MA_HO_SO),
	FOREIGN KEY (LOAI_KET_CAU_KHUNG) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_KET_CAU_MONG) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_KET_CAU_TUONG_NHA) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_KET_CAU_NEN) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_KET_CAU_MAI) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_TRANG_TRI_NOI_THAT) REFERENCES THAM_SO(MA_THAM_SO),
	FOREIGN KEY (LOAI_CONG_TRINH_XAY_DUNG) REFERENCES THAM_SO(MA_THAM_SO)
)
INSERT INTO VAT_LIEU_XAY_DUNG VALUES (1,41,NULL,45,NULL,49,NULL,53,NULL,57,NULL,10,10,10,10,10,10,10,10,10,10,1,1,61,NULL,65,NULL)